﻿@page "/门店销售按产品分析表"
@using System.Diagnostics.CodeAnalysis
@using BootstrapBlazor.OnlyServerZhongShun.Data.dbmodels
@using System.Data
@inject ILogger<Form7> NormalLogger
@inject AuthenticationStateProvider authenticationStateProvider
@inject ToastService ToastService
@inject IConfiguration _congifuration
@inject SqlSugarScope _SqlSugarScope
@attribute [Authorize(Roles = "超级管理员,业务总,业务经理,业务员")]


<HeadContent>
    <style>
        .checkbox-list {
            --bb-checkbox-item-width: 80px;
        }

        .multi-select {
            --bb-multi-select-min-height: 26px;
        }

    </style>
</HeadContent>

<div class="row g-1">
    <div class="col-6">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="查询日期" />
            <DateTimeRange @bind-Value="@NormalDateTimeRangeValue" OnConfirm="OnNormalConfirm" ShowSidebar="true" ViewMode="DatePickerViewMode.Date" IsEditable="true" DateFormat="yyyy-MM-dd">
                <TimePickerSetting ShowClockScale="true" IsAutoSwitch="false" />
            </DateTimeRange>
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="客户" />
            <Select IsVirtualize="true" Items="客户列表Items" @bind-Value="选中客户" OnSearchTextChanged="OnSearchTextChanged" ShowSearch="true" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>

    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="门店" />
            <MultiSelect Items="@客商门店对照Items" @bind-Value="@选中门店List" OnSelectedItemsChanged="@门店选择改变事件" ShowToolbar="true" />
        </BootstrapInputGroup>
    </div>

    <div class="col-6 col-sm-6">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="部门" />
            <MultiSelect Items="@部门Items" @bind-Value="@选中部门List" OnSelectedItemsChanged="@部门选择改变事件" ShowToolbar="true" />
        </BootstrapInputGroup>
    </div>
    <div class="col-3 col-sm-3">
        <RadioList TValue="string" Items="@查询类别" @bind-Value="@选中查询类别" Class="custom-radio7" />
    </div>
    <div class="col-6 col-sm-4 col-md-3 col-lg-auto">
        <Button OnClick="@Button_chaxun_Click" IsAsync="true" Color="Color.Primary">查询</Button>
        <Button OnClick="@Button_daochu" Color="Color.Info">导出</Button>
    </div>

</div>
@* <div class="table-fixed-header"> *@
<Table class="table" TItem="DynamicObject" DynamicContext="DataTableDynamicContext"
       IsBordered="true" IsMultipleSelect="false" ShowToolbar="true" ShowRefresh="false" ShowDefaultButtons="false" IsPagination="true" ShowExportButton="false"
       OnExportAsync="OnExportAsync" OnSort="OnSort" ShowFooter="true" IsStriped="true" PageItemsSource="new int[]{1000,2000,3000,4000,5000,10000}" TableSize="TableSize.Compact"
       Height="440" IsFixedHeader="true">

    <FooterTemplate>
        <tr>
            @if (DataTableDynamicContext != null && DataTableDynamicContext.DataTable.Rows.Count > 0)
            {
                @foreach (DataColumn property in DataTableDynamicContext.DataTable.Columns)
                {
                    <td>
                        @*  //如果不是 代理商名称,部门 列 *@
                        @* @if (property.ColumnName != "代理商名称" && property.ColumnName != "部门" && property.ColumnName != "销售员" && Type.GetTypeCode(property.DataType) == TypeCode.String) *@
                        @if (Type.GetTypeCode(property.DataType) != TypeCode.String && property.ColumnName != "库存率" && property.ColumnName != "序号")
                        {
                            //当前列的合计
                            var sum = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull(property.ColumnName)) return 0m;
                                object value = dr[property.ColumnName];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            @sum
                        }
                        else if (property.ColumnName == "序号")
                        {
                            @:合计
                        }
                        else if (property.ColumnName == "库存率")
                        {
                            decimal 分母 = 0;
                            //判断是否包含 门店库存 列，如果包含，就计算合计值
                            if (DataTableDynamicContext.DataTable.Columns.Contains("按售价发货"))
                            {
                                //当前列的合计
                                var sum = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                                {
                                    if (dr.IsNull("按售价发货")) return 0m;
                                    object value = dr["按售价发货"];
                                    if (value is int)
                                        return (decimal)(int)value;
                                    else if (value is decimal)
                                        return (decimal)value;
                                    else
                                        return 0m;
                                });
                                分母 = sum;
                            }
                            else if (DataTableDynamicContext.DataTable.Columns.Contains("发货重量"))
                            {
                                //当前列的合计
                                var sum = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                                {
                                    if (dr.IsNull("发货重量")) return 0m;
                                    object value = dr["发货重量"];
                                    if (value is int)
                                        return (decimal)(int)value;
                                    else if (value is decimal)
                                        return (decimal)value;
                                    else
                                        return 0m;
                                });
                                分母 = sum;
                            }

                            var 分子 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("门店库存")) return 0m;
                                object value = dr["门店库存"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });

                            if (分母 != 0)
                            {
                                @((分子 / 分母).ToString("P2"));
                                ;
                            }

                        }
                        else
                        {
                            @:--
                        }

                    </td>
                }
            }

        </tr>
    </FooterTemplate>
</Table>
@* </div> *@

@code {
    private string username;

    [Inject]
    [NotNull]
    private ITableExport? TableExport { get; set; }
    [Inject]
    [NotNull]
    private ToastService? Toast { get; set; }

    private List<View_Custom> View_CustomList = new List<View_Custom>();
    private List<T客商门店对照> 客商门店对照List = new List<T客商门店对照>();
    private List<View_Depart> View_DepartList = new List<View_Depart>();

    //时间范围
    private DateTimeRangeValue NormalDateTimeRangeValue { get; set; } = new();

    private IEnumerable<string> 选中部门List { get; set; }
    private string 选中部门InStr = "";
    private IEnumerable<SelectedItem> 部门Items => View_DepartList.Select(i => new SelectedItem(i.cDepCode!, i.cDepName!)).ToList();

    private IEnumerable<SelectedItem> 客户列表Items => View_CustomList.Select(i => new SelectedItem(i.cCusCode!, i.cCusName!)).ToList();
    private IEnumerable<SelectedItem> 客商门店对照Items => 客商门店对照List.Select(i => new SelectedItem(i.posName!, i.posName! + "------U8(" + i.U8name! + ")")).ToList();
    [NotNull]
    private IEnumerable<SelectedItem>? 查询类别 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "金额", Value = "0" },
        new SelectedItem { Text = "数量", Value = "1"}
    });


    private SelectedItem? 选中客户 { get; set; }
    private IEnumerable<string> 选中门店List { get; set; }
    private string 选中门店InStr = "";
    private string 选中查询类别 { get; set; }

    //表格数据
    [NotNull]
    private DataTableDynamicContext? DataTableDynamicContext { get; set; }
    private DataTableDynamicContext? DataTableDynamicContextDetail { get; set; }


    protected override async void OnInitialized()
    {
        var authState = await authenticationStateProvider.GetAuthenticationStateAsync();
        var user = authState.User;
        username = user.Identity.Name;
        //今年1月1日
        NormalDateTimeRangeValue.Start = new DateTime(DateTime.Now.Year, 1, 1);
        //今天
        NormalDateTimeRangeValue.End = DateTime.Now.AddDays(-1);

        View_CustomList = _SqlSugarScope.Queryable<View_Custom>().ToList();
        //加一个请选择
        View_CustomList.Insert(0, new View_Custom { cCusCode = "", cCusName = "请选择" });

        //读取配置文件
        UserDataAuth userDataAuth = _SqlSugarScope.Queryable<UserDataAuth>().Where(it => it.UserName == username).First();
        List<Guid> guids = userDataAuth.CanViewDepart.Split(",").ToList().Select(it => Guid.Parse(it)).ToList();
        List<Sys_Department> depts = _SqlSugarScope.Queryable<Sys_Department>().Where(it => guids.Contains(it.DepartmentId)).ToList();
        List<string> deptNames = depts.Select(it => it.DepartmentName).ToList();
        var PaiChuBuMen = _congifuration.GetSection("PaiChuBuMen").Value;
        List<View_Depart> deptss = _SqlSugarScope.Queryable<View_Depart>().Where($"cDepCode not in ({PaiChuBuMen})").ToList();
        View_DepartList = deptss.Where(it => deptNames.Contains(it.cDepName)).ToList();

        客商门店对照List = _SqlSugarScope.Queryable<T客商门店对照>().OrderBy(it => it.U8客户编码).ToList();
        // //加一个请选择
        // View_DepartList.Insert(0, new View_Depart { cDepCode = "", cDepName = "请选择" });

    }
    private Task OnNormalConfirm(DateTimeRangeValue value)
    {
        NormalLogger.LogDebug($"选择的时间范围是: {value.Start:yyyy-MM-dd HH:mm:ss} - {value.End:yyyy-MM-dd HH:mm:ss}");
        //遍历输出选中的客户
        if (选中客户 != null)
        {
            NormalLogger.LogDebug($"选中的客户是: {选中客户.Text}");
        }
        //遍历输出选中的部门
        if (选中门店List != null)
        {
            foreach (var item in 选中门店List)
            {

            }
        }
        return Task.CompletedTask;
    }
    private IEnumerable<SelectedItem> OnSearchTextChanged(string searchText)
    {
        return View_CustomList.Where(i => i.cCusName!.Contains(searchText, StringComparison.OrdinalIgnoreCase)).Select(i => new SelectedItem(i.cCusCode!, i.cCusName!));
    }

    private Task 门店选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中门店InStr = string.Join("','", items.Select(it => it.Value).ToArray());
        return Task.CompletedTask;
    }
    private Task 部门选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中部门InStr = string.Join("','", items.Select(it => it.Text).ToArray());
        return Task.CompletedTask;
    }

    private async Task<bool> OnExportAsync(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), context.Columns, $"原06代理业务数据穿透分析_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }

    private async void Button_daochu(MouseEventArgs e)
    {
        //复制一个table用来导出
        DataTable dt = DataTableDynamicContext.DataTable.Copy();
        //增加一行合计
        DataRow dr = dt.NewRow();

        dr["门店销售"] = dt.AsEnumerable().Sum(dr =>
                                {
                                    if (dr.IsNull("门店销售")) return 0m;
                                    object value = dr["门店销售"];
                                    if (value is int)
                                        return (decimal)(int)value;
                                    else if (value is decimal)
                                        return (decimal)value;
                                    else
                                        return 0m;
                                });
        dr["门店库存"] = dt.AsEnumerable().Sum(dr =>
        {
            if (dr.IsNull("门店库存")) return 0m;
            object value = dr["门店库存"];
            if (value is int)
                return (decimal)(int)value;
            else if (value is decimal)
                return (decimal)value;
            else
                return 0m;
        });


        if (选中查询类别 == "0")
        {
            dr["公司发货净额"] = dt.AsEnumerable().Sum(dr =>
                                {
                                    if (dr.IsNull("公司发货净额")) return 0m;
                                    object value = dr["公司发货净额"];
                                    if (value is int)
                                        return (decimal)(int)value;
                                    else if (value is decimal)
                                        return (decimal)value;
                                    else
                                        return 0m;
                                });
            dr["按售价发货"] = dt.AsEnumerable().Sum(dr =>
                                {
                                    if (dr.IsNull("按售价发货")) return 0m;
                                    object value = dr["按售价发货"];
                                    if (value is int)
                                        return (decimal)(int)value;
                                    else if (value is decimal)
                                        return (decimal)value;
                                    else
                                        return 0m;
                                });
            dr["库存率"] = (decimal)dr["按售价发货"] == 0 ? 0 : (decimal)dr["门店库存"] / (decimal)dr["按售价发货"];
        }
        else
        {
            dr["公司发货重量"] = dt.AsEnumerable().Sum(dr =>
                                {
                                    if (dr.IsNull("公司发货重量")) return 0m;
                                    object value = dr["公司发货重量"];
                                    if (value is int)
                                        return (decimal)(int)value;
                                    else if (value is decimal)
                                        return (decimal)value;
                                    else
                                        return 0m;
                                });

            dr["发货重量"] = dt.AsEnumerable().Sum(dr =>
                               {
                                   if (dr.IsNull("发货重量")) return 0m;
                                   object value = dr["发货重量"];
                                   if (value is int)
                                       return (decimal)(int)value;
                                   else if (value is decimal)
                                       return (decimal)value;
                                   else
                                       return 0m;
                               });
            dr["库存率"] = (decimal)dr["发货重量"] == 0 ? 0 : (decimal)dr["门店库存"] / (decimal)dr["发货重量"];
        }


        dt.Rows.Add(dr);
        DataTableDynamicContext dataTable = new DataTableDynamicContext(dt);
        var ret = await TableExport.ExportExcelAsync(dataTable.GetItems(), DataTableDynamicContext.GetColumns(), $"门店进销存明细表_{DateTime.Now:yyyyMMddHHmmss}.xlsx");


        // 导出符合条件的所有数据 data
        // var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), DataTableDynamicContext.GetColumns(), $"门店销售分析_{DateTime.Now:yyyyMMddHHmmss}.xlsx");
        ToastService.Success("提示", "导出成功");
    }

    private string OnSort(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContext.DataTable.Clone();

        DataView dv = DataTableDynamicContext.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();
        for (int i = 0; i < sortTable.Rows.Count; i++)
        {
            sortTable.Rows[i]["序号"] = i + 1;
        }
        DataTableDynamicContext = new DataTableDynamicContext(sortTable, (context, col) =>

            {
                if (col.GetFieldName() == "库存率")
                {
                    col.FormatString = "P2";
                }
                else if (col.GetFieldName() == "序号")
                {
                    col.Width = 40;
                }
                else if (col.GetFieldName() == "类别")
                {
                    col.Width = 40;
                }
                else if (col.GetFieldName() == "门店")
                {
                    col.Width = 100;
                }
                //所有列可排序
                col.Sortable = true;
                col.DefaultSort = true;
            });
        StateHasChanged();
        return sortString;
    }

    private async Task<bool> OnExportDetailAsync(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContextDetail.GetItems(), context.Columns, $"原06代理业务数据穿透分析明细_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }

    private string OnSortDetail(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContextDetail.DataTable.Clone();

        DataView dv = DataTableDynamicContextDetail.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();

        DataTableDynamicContextDetail = new DataTableDynamicContext(sortTable, (context, col) =>

            {
                //所有列可排序
                col.Sortable = true;
                col.DefaultSort = true;
                if (col.GetFieldName() == "库存率")
                {
                    col.FormatString = "P2";
                }
                else if (col.GetFieldName() == "序号")
                {
                    col.Width = 40;
                }
                else if (col.GetFieldName() == "类别")
                {
                    col.Width = 40;
                }
                else if (col.GetFieldName() == "门店")
                {
                    col.Width = 100;
                }
            });
        StateHasChanged();
        return sortString;
    }

    private decimal sum1;
    private decimal sum2;
    private decimal sum3;
    private decimal sum4;
    private decimal sum5;
    private decimal sum6;
    private decimal sum7;
    private decimal sum8;

    private async void Button_chaxun_Click(MouseEventArgs e)
    {
        sum1 = 0;
        sum2 = 0;
        sum3 = 0;
        sum4 = 0;
        sum5 = 0;
        sum6 = 0;
        sum7 = 0;
        sum8 = 0;
        //客户发货排名
        string sql;
        string 年度 = this.NormalDateTimeRangeValue.End.Year.ToString();
        string 今年开始日期 = this.NormalDateTimeRangeValue.Start.ToString("yyyy-MM-dd");
        string 今年结束日期 = this.NormalDateTimeRangeValue.End.ToString("yyyy-MM-dd 23:59:59");
        string 去年开始日期 = this.NormalDateTimeRangeValue.Start.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年结束日期 = this.NormalDateTimeRangeValue.End.AddYears(-1).ToString("yyyy-MM-dd 23:59:59");
        string 去年 = 去年开始日期.Substring(0, 4);

        // string loginuser = username;
        string loginuser = "admin";
        if (string.IsNullOrEmpty(选中门店InStr) == true)
        {
            选中门店InStr += string.Join("','", 客商门店对照List.Select(it => it.posName));
        }

        if (string.IsNullOrEmpty(选中部门InStr) == true)
        {
            选中部门InStr += string.Join("','", View_DepartList.Select(it => it.cDepName));
        }
        string 部门清单 = "('" + 选中部门InStr + "')";

        string 门店清单 = "('" + 选中门店InStr + "')";
        string guid = Guid.NewGuid().ToString();
        sql = "delete T门店销售分析按产品 where usercode='" + loginuser + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        switch (选中查询类别)
        {
            case "0":
                {
                    DataTableDynamicContextDetail = null;
                    //先塞U8的
                    #region
                    sql = "insert into  T门店销售分析按产品 (guid, usercode,  cCusCode,dDate,按售价发货,门店销售,销售发票净额,cInvCode)";
                    sql += " select '" + guid + "','" + loginuser + "', cCusCode,dDate,iMoney,0,0,cInvCode ";
                    sql += " from View_发票明细POSInv  where 1=1 ";
                    sql += " and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = "insert into  T门店销售分析按产品 (guid, usercode,  cCusCode,dDate,按售价发货,门店销售,销售发票净额,cInvCode)";
                    sql += " select '" + guid + "','" + loginuser + "', cCusCode,dDate,0,0, iMoney,cInvCode";
                    sql += " from View_发票明细  where 1=1 ";
                    sql += " and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    #endregion
                    //再塞POS的
                    #region
                    sql = "insert into  T门店销售分析按产品 (guid, usercode,  U8name,dDate,按售价发货,门店销售,门店,cInvCode,类别,产品名称)";
                    sql += " select '" + guid + "','" + loginuser + "', U8name,日期时间,0,金额,机构名称,商品编号,类别,商品名称 ";
                    sql += " from View_门店销售额 where 1=1 ";
                    sql += " and 日期时间>='" + 今年开始日期 + "' and 日期时间<='" + 今年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    #endregion
                    //更新POS门店名称
                    #region


                    sql = " update a set a.门店=b.[posName] ,a.U8name = b.U8name  ";
                    sql += " from  T门店销售分析按产品 a ,T客商门店对照 b ";
                    sql += " where a.guid='" + guid + "' and a.cCusCode=b.U8客户编码 ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    sql = " update a set a.门店=b.[posName]   ";
                    sql += " from  T门店销售分析按产品 a ,T客商门店对照 b ";
                    sql += " where a.guid='" + guid + "' and a.U8name=b.U8name ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = " update a set a.部门名称=b.cDepName   ";
                    sql += " from  T门店销售分析按产品 a ,View_CustomDepart b ";
                    sql += " where a.guid='" + guid + "' and a.U8name=b.cCusName ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                     //更新产品
                    sql = "update  a set a.产品名称=b.cInvName,a.类别=b.类别名称 ";
                    sql += " from T门店销售分析按产品 a, View_Inventory b where a.cInvCode=b.cInvCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

                    #endregion

                    sql = "SELECT   部门名称, 门店,类别,产品名称, Convert(decimal(18,4),sum(销售发票净额)/10000) 公司发货净额,Convert(decimal(18,4),sum(按售价发货)/10000) 按售价发货 , Convert(decimal(18,4),sum(门店销售)/10000) 门店销售,(sum(按售价发货)-sum(门店销售))/10000 门店库存 ,0.0 库存率  FROM      T门店销售分析按产品  ";
                    sql += " where guid='" + guid + "' and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
                    sql += " and 门店 is not null";
                    sql += " and 门店 in " + 门店清单;
                    sql += " and 部门名称 in " + 部门清单;
                    if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                    {
                        sql += " and U8name like '%" + 选中客户?.Text + "%'";
                    }
                    sql += " group by 部门名称, 门店,类别,产品名称  ";
                    DataTable dataTableHZ = _SqlSugarScope.Ado.GetDataTable(sql);

                    if (dataTableHZ != null)
                    {

                        for (int i = 0; i < dataTableHZ.Rows.Count; i++)
                        {
                            DataRow dr = dataTableHZ.Rows[i];
                            decimal 库存率 = 0;
                            decimal.TryParse(dr["门店销售"].ToString(), out decimal 门店销售);
                            // if (门店销售 == 0)
                            // {
                            //     dataTableHZ.Rows[i]["库存率"] = 0;
                            //     dataTableHZ.Rows[i]["门店库存"] = 0.0;
                            // }
                            // else 
                            if (decimal.TryParse(dr["按售价发货"].ToString(), out decimal result1) == true && result1 != 0)
                            {
                                // decimal a = Pub.ToDecimal(dr["门店库存"].ToString());
                                // decimal b = Pub.ToDecimal(dr["按售价发货"].ToString());
                                decimal.TryParse(dr["门店库存"].ToString(), out decimal a);
                                decimal.TryParse(dr["按售价发货"].ToString(), out decimal b);
                                库存率 = (a / b);
                                dataTableHZ.Rows[i]["库存率"] = 库存率;

                            }
                            else
                            {
                                dataTableHZ.Rows[i]["库存率"] = 0;
                            }
                        }
                        if (dataTableHZ != null && dataTableHZ.Rows.Count > 0)
                        {
                            dataTableHZ.Columns.Add("序号", typeof(int)).SetOrdinal(0);
                            for (int i = 0; i < dataTableHZ.Rows.Count; i++)
                            {
                                dataTableHZ.Rows[i]["序号"] = i + 1;
                            }
                        }
                        DataTableDynamicContext = new DataTableDynamicContext(dataTableHZ, (context, col) =>
                         {
                             if (col.GetFieldName() == "库存率")
                             {
                                 col.FormatString = "P2";
                             }
                             else if (col.GetFieldName() == "序号")
                             {
                                 col.Width = 40;
                             }
                             else if (col.GetFieldName() == "类别")
                             {
                                 col.Width = 40;
                             }
                             else if (col.GetFieldName() == "门店")
                             {
                                 col.Width = 100;
                             }
                             //所有列可排序
                             col.Sortable = true;
                             col.DefaultSort = true;
                         });
                        StateHasChanged();
                    }
                    break;
                }


            default:
                {
                    DataTableDynamicContext = null;

                    //先塞U8的
                    #region
                    sql = "insert into  T门店销售分析按产品 (guid, usercode,  cCusCode,dDate,发货重量,门店销售,销售发票净额,cInvCode)";
                    sql += " select '" + guid + "','" + loginuser + "', cCusCode,dDate,iQuantity,0,0,cInvCode ";
                    sql += " from View_发票明细POSInv where 1=1 ";
                    sql += " and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = "insert into  T门店销售分析按产品 (guid, usercode,  cCusCode,dDate,按售价发货,门店销售,销售发票净额,cInvCode)";
                    sql += " select '" + guid + "','" + loginuser + "', cCusCode,dDate,0,0, iQuantity,cInvCode";
                    sql += " from View_发票明细  where 1=1 ";
                    sql += " and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    #endregion
                    //再塞POS的
                    #region
                    sql = "insert into  T门店销售分析按产品 (guid, usercode,  U8name,dDate,发货重量,门店销售,cInvCode,类别,产品名称)";
                    sql += " select '" + guid + "','" + loginuser + "', U8name,日期时间,0,数量 ,商品编号,类别,商品名称";
                    sql += " from View_门店销售额 where 1=1 ";
                    sql += " and 日期时间>='" + 今年开始日期 + "' and 日期时间<='" + 今年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    #endregion
                    //更新POS门店名称
                    #region


                    sql = " update a set a.门店=b.[posName] ,a.U8name = b.U8name  ";
                    sql += " from  T门店销售分析按产品 a ,T客商门店对照 b ";
                    sql += " where a.guid='" + guid + "' and a.cCusCode=b.U8客户编码 ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    sql = " update a set a.门店=b.[posName]   ";
                    sql += " from  T门店销售分析按产品 a ,T客商门店对照 b ";
                    sql += " where a.guid='" + guid + "' and a.U8name=b.U8name ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = " update a set a.部门名称=b.cDepName   ";
                    sql += " from  T门店销售分析按产品 a ,View_CustomDepart b ";
                    sql += " where a.guid='" + guid + "' and a.U8name=b.cCusName ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    //更新产品
                    sql = "update  a set a.产品名称=b.cInvName,a.类别=b.类别名称 ";
                    sql += " from T门店销售分析按产品 a, View_Inventory b where a.cInvCode=b.cInvCode and a.guid='" + guid + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    #endregion

                    sql = "SELECT    部门名称, 门店,类别,产品名称,sum(销售发票净额) 公司发货重量, sum(发货重量) 发货重量 , sum(门店销售) 门店销售,sum(发货重量)-sum(门店销售) 门店库存 ,0.0 库存率  FROM      T门店销售分析按产品  ";
                    sql += " where guid='" + guid + "' and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
                    sql += " and 门店 is not null";
                    sql += " and 门店 in " + 门店清单;
                    sql += " and 部门名称 in " + 部门清单;
                    if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                    {
                        sql += " and 门店 like '%" + this.选中客户.Text + "%'";
                    }
                    sql += " group by 部门名称,门店,类别,产品名称  ";
                    DataTable dataTableHZ2 = _SqlSugarScope.Ado.GetDataTable(sql);

                    if (dataTableHZ2 != null)
                    {

                        for (int i = 0; i < dataTableHZ2.Rows.Count; i++)
                        {

                            DataRow dr = dataTableHZ2.Rows[i];
                            decimal 库存率 = 0;
                            //如果 门店库存 为0，库存率为0
                            // decimal.TryParse(dr["门店销售"].ToString(), out decimal 门店销售);
                            // if (门店销售 == 0)
                            // {
                            //     dataTableHZ2.Rows[i]["库存率"] = 0;
                            //     dataTableHZ2.Rows[i]["门店库存"] = 0.0;
                            // }
                            // else
                             if (decimal.TryParse(dr["发货重量"].ToString(), out decimal result1) == true && result1 != 0)
                            {
                                // decimal a = Pub.ToDecimal(dr["门店库存"].ToString());
                                // decimal b = Pub.ToDecimal(dr["发货重量"].ToString());
                                decimal.TryParse(dr["门店库存"].ToString(), out decimal a);
                                decimal.TryParse(dr["发货重量"].ToString(), out decimal b);
                                库存率 = (a / b);
                                dataTableHZ2.Rows[i]["库存率"] = 库存率;

                            }
                            else
                            {
                                dataTableHZ2.Rows[i]["库存率"] = 0;
                            }
                        }

                        if (dataTableHZ2 != null && dataTableHZ2.Rows.Count > 0)
                        {
                            if (dataTableHZ2.Columns.Contains("序号") == false)
                        {
                            dataTableHZ2.Columns.Add("序号", typeof(int)).SetOrdinal(0);
                        }

                            for (int i = 0; i < dataTableHZ2.Rows.Count; i++)
                            {
                                dataTableHZ2.Rows[i]["序号"] = i + 1;
                            }
                        }
                        DataTableDynamicContext = new DataTableDynamicContext(dataTableHZ2, (context, col) =>
                        {
                            if (col.GetFieldName() == "库存率")
                            {
                                col.FormatString = "P2";
                            }
                            else if (col.GetFieldName() == "序号")
                            {
                                col.Width = 40;
                            }
                            else if (col.GetFieldName() == "类别")
                            {
                                col.Width = 40;
                            }
                            else if (col.GetFieldName() == "门店")
                            {
                                col.Width = 100;
                            }
                            //所有列可排序
                            col.Sortable = true;
                            col.DefaultSort = true;
                        });
                    }
                    break;
                }
        }
        await ToastService.Information("提示", "查询成功");
    }
}
